package net.tycmc.cty.ctyms.timejob.iems.dao;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.tycmc.bulb.common.dao.IBaseDAO;
import net.tycmc.bulb.common.dao.ITransactionDAO;
import net.tycmc.bulb.common.util.MapGetter;
import net.tycmc.bulb.common.util.StringUtil;
import net.tycmc.bulb.dbutil.global.GlobalDBVar;
import net.tycmc.cty.ctyms.timejob.common.ListUtil;

/**
 * 进出围栏相关表的DAO操作
 * 
 * @modify 
 * @remark 5分钟执行一次
 */
public class IOFenTipDao {
	private SMSInfoDAO smsInfoDAO;
	IBaseDAO baseDAO;
	private ITransactionDAO tranDAO;

	public IBaseDAO getBaseDAO() {
		return baseDAO;
	}

	public void setBaseDAO(IBaseDAO baseDAO) {
		this.baseDAO = baseDAO;
	}

	public ITransactionDAO getTranDAO() {
		return tranDAO;
	}

	public void setTranDAO(ITransactionDAO tranDAO) {
		this.tranDAO = tranDAO;
	}

	public SMSInfoDAO getSmsInfoDAO() {
		return smsInfoDAO;
	}

	public void setSmsInfoDAO(SMSInfoDAO smsInfoDAO) {
		this.smsInfoDAO = smsInfoDAO;
	}

	/**
	 * 获取设备与围栏关系信息 20141016jinjiayu
	 * @param termSoftVer414 JPP设备
	 * @return
	 */
	public List<Map<String, Object>> getVFs(String termSoftVer414) {
		
		/*String sql = "select ISNULL(fltMR_MsgF_ID,0) id from fltMsgRecord where fltMR_TableName='IOFEN'";
		Map<String,Object> mapID = baseDAO.execQueryOne(sql, new Object[]{});*/

		List<Map<String, Object>> list = null;
		
		StringBuffer sb = new StringBuffer("select VehicleID,dbcode,VclPL_ID,VclPL_Lo,VclPL_La,VehicleNumber,FenO_FenceID,VclPL_PstnTime,FenceName,OrgFen_OrgCode,infen = CASE WHEN vclpl_lo >= OrgFen_RectLeftTX AND vclpl_lo <= OrgFen_RectRightBX AND vclpl_la <= OrgFen_RectLeftTY AND vclpl_la >= OrgFen_RectRightBY THEN 1 ELSE 0 END,VclPS_isOpen,VclPL_MsgID,VclPL_MsgTp_ID");
		sb.append(" FROM cty_vcl.dbo.vcl_pstn_last LEFT JOIN v_vehicleinfo ON vehicleid = vclpl_vcl_id");
		sb.append(" LEFT JOIN fence_org ON dbcode LIKE fence_org.feno_orgcode + '%'");
		sb.append(" LEFT JOIN organization_fencelist ON (organization_fencelist.orgfen_orgcode LIKE fence_org.feno_orgcode + '%' or");
		sb.append(" organization_fencelist.orgfen_orgcode LIKE ','+fence_org.feno_orgcode + '%') AND fenceid = feno_fenceid");
		sb.append(" left join " +GlobalDBVar.getDBFullName_Conn("VclPromptSwitch","1",null)+
				" on vclpl_vcl_id=VclPS_VclID and VclPS_Type='7'");
		sb.append(" where UsefulLife > GETDATE() and Isnull(VclPL_Lo,'') <> '' AND VclPL_Lo != 0 AND Isnull(vclpl_la,'') <> '' AND VclPL_La != 0 AND Isnull(FenO_FenceID,'') <> '' and dbcode is not null and vclpl_pstntime is not null and ISNULL(orgfen_orgcode,'') <> '' AND fencetype = 3 AND orgfen_state = 1 AND Tmnl_SoftEdition IN ("+termSoftVer414.toString()+") and OrgFen_isAlarm=1");
		//sb.append(" where VclPL_ID>? and Isnull(VclPL_Lo,'') <> '' AND VclPL_Lo != 0 AND Isnull(vclpl_la,'') <> '' AND VclPL_La != 0 AND Isnull(FenO_FenceID,'') <> '' and dbcode is not null and vclpl_pstntime is not null and ISNULL(orgfen_orgcode,'') <> '' AND fencetype = 3 AND orgfen_state = 1 AND Tmnl_SoftEdition IN ("+termSoftVer414.toString()+") and OrgFen_isAlarm=1");
		sb.append(" ORDER BY LEN(Fence_Org.FenO_OrgCode),Fence_Org.FenO_OrgCode");
		
		list = this.baseDAO.execQuery(sb.toString(),new Object[] { });
				
		/*sql = "select MAX(VclPL_ID) id from cty_vcl.dbo.vcl_pstn_last";
		mapID = baseDAO.execQueryOne(sql, new Object[]{});
		
		sql = "update fltMsgRecord set fltMR_MsgF_ID=? where fltMR_TableName='IOFEN'";
		baseDAO.execUpdate(sql, new Object[]{MapGetter.getString(mapID, "id") });*/
		
		
		return list;
	}

	/**
	 * 获取设备基本信息 20141016jinjiayu
	 * @param termSoftVer414 JPP设备
	 * @return
	 */
	public List<Map<String, Object>> getVD(String termSoftVer414) {
		
		List<Map<String, Object>> list = null;
		List<Map<String, Object>> gps = null;
		SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd"); 
		String date =  formatter.format(new Date());
		
		StringBuffer sb = new StringBuffer("SELECT MsgTL_Vcl_ID,MsgTL_tWorkTime_UDS,MsgTL_tRunKm,MsgTL_tRunKmExact,MsgTL_tRunKmECU,MsgTL_tRunKmExact_PGN,MsgTL_tOilCons");
		sb.append(" FROM CTY_Egn_B.DBO.Msg_Timing_Last");
		//sb.append(" left join Eff_Config on MsgTL_Vcl_ID=EffC_VclID");
		sb.append(" left join V_VehicleInfo on VehicleID=msgtL_vcl_id");
		//sb.append(" WHERE EffC_isOpen=1 AND Tmnl_SoftEdition IN ("+termSoftVer414.toString()+")");
		sb.append(" WHERE Tmnl_SoftEdition IN ("+termSoftVer414.toString()+")");
		list = this.baseDAO.execQuery(sb.toString(),new Object[] {});
		
		String sb_gps = "select VclPL_Vcl_ID,VclPL_TMileage from CTY_Vcl.dbo.Vcl_Pstn_Last";
				
		gps = this.baseDAO.execQuery(sb_gps,new Object[] {});
		
		for(Map<String, Object> map : list){
			for(Map<String, Object> map2 : gps)
				if(MapGetter.getString(map, "MsgTL_Vcl_ID").equals(MapGetter.getString(map2, "VclPL_Vcl_ID")))
					map.put("gps", MapGetter.getString(map2, "VclPL_TMileage"));
		}
		
		return list;
	}

	/**
	 * 获取围栏内设备 20141016jinjiayu
	 * @param termSoftVer414 JPP设备
	 * @return
	 */
	public Map<String,List<Map<String,Object>>> getVS(String termSoftVer414) {
		List<Map<String, Object>> list = null;
		Map<String,List<Map<String,Object>>>  map = null;
		
		String sb = "select VclFSL_VclID,VclFSL_FenceID from VclFenState_Last left join V_VehicleInfo on VehicleID=VclFSL_VclID where isnull(VclFSL_VclID,'')<>'' and isnull(VclFSL_FenceID,'')<>'' AND Tmnl_SoftEdition IN ("+termSoftVer414.toString()+")";
		list = this.baseDAO.execQuery(sb,new Object[] {});
		
		map = new ListUtil().groupBy("VclFSL_VclID",list);
		
		return map;
	}

	/**
	 * 向短信表及提示表插入数据20141016jinjiayu
	 * @param vclid  	设备id
	 * @param fenid		围栏id
	 * @param gpstime	进出围栏时间
	 * @param inFen		1/0进/出围栏
	 * @param SMSContent	短信表内容
	 * @param SMSContentPro		提示表内容
	 * @param map2		设备基本信息数组
	 * @param sqlList
	 * @param paramList
	 * @return
	 */
	public boolean  insertPro(String vclid, String fenid, String gpstime,String inFen, String SMSContent, 
			String SMSContentPro, Map<String, Object> map2,String lo,String la, String position,String msgpid,String tpid,List<String> sqlList, List<Object[]> paramList) {
		String runKm = null==map2?null:MapGetter.getString(map2, "MsgTL_tRunKm");
		String runKmExact = null==map2?null:MapGetter.getString(map2, "MsgTL_tRunKmExact");
		String ECUKm = null==map2?null:MapGetter.getString(map2, "MsgTL_tRunKmECU");
		String meterKm = null==map2?null:MapGetter.getString(map2, "MsgTL_tRunKmExact_PGN");
		String GPSKm = null==map2?null:MapGetter.getString(map2, "gps");
		String workTime = null==map2?null:MapGetter.getString(map2, "MsgTL_tWorkTime_UDS");
		String oilCon = null==map2?null:MapGetter.getString(map2, "MsgTL_tOilCons");
		
		if(!StringUtil.isNull(vclid) || !StringUtil.isNull(fenid) || !StringUtil.isNull(inFen))
			return false;
			
		String sb = "insert into PassFenTip values(?,?,?,?,?,?,?,?,?,?,?,?)";
		
		StringBuffer sb2 = new StringBuffer("insert into Msg_Prompt(MsgP_VclID,MsgP_MsgTime,MsgP_Type,MsgP_CreateTime,Msgp_SecTypeID,Msgp_SecTypeName");
		sb2.append(" ,MsgP_DetailMsg,MsgP_Position,MsgP_Lo,MsgP_La,MsgP_MsgCID,MsgP_MsgID)");
		sb2.append(" values(?,?,'15',GETDATE(),?,?,?,?,?,?,IDENT_CURRENT('Msg_Prompt'),IDENT_CURRENT('PassFenTip'))");
		
		sqlList.add(sb);
		sqlList.add(sb2.toString());
		paramList.add(new Object[] { 
				vclid,
				fenid,
				"1".equals(inFen)?"1":"0",
				gpstime,
				workTime,
				runKm,
				GPSKm,
				meterKm,
				runKmExact,
				ECUKm,
				oilCon,
				msgpid+"_"+tpid});
		paramList.add(new Object[] { 
				vclid,
				gpstime,
				inFen,
				"1".equals(inFen)?"进围栏提示":"出围栏提示",
				SMSContentPro,
				position,
				lo,
				la
				});
		return true;
	}

	/**
	 * 删除围栏内数据 20141016jinjiayu
	 * @param vclid		设备id
	 * @param fenid		围栏id
	 * @param sqlList
	 * @param paramList
	 */
	public void deleteVS(String vclid, String fenid, List<String> sqlList, List<Object[]> paramList) {
		
		String sb = "delete VclFenState_Last where VclFSL_VclID=? and VclFSL_FenceID=? ";
		
		sqlList.add(sb);
		paramList.add(new Object[] { vclid,fenid});
		
		return ;
	}
	
	/**
	 * 发短信 20141016jinjiayu
	 * @param sqlList
	 * @param paramList
	 * @param userList		用户组
	 * @param smsSendTimeList	时间
	 * @param SMSContent	短信内容
	 * @param vclorgcode	设备所在的组织机构
	 * @param fenorgcode	围栏所在的组织机构
	 */
	public void IOSMSInfo(List<String> sqlList, List<Object[]> paramList,List<Map<String, Object>> userList, List<String> smsSendTimeList, 
			String SMSContent,  String vclorgcode, String fenorgcode,String gpstime,String vclid,String iofen) {
		
		String[] fencodes = null;
		String[] usercodes = null;
		int i=0,h=0;
		
		for(Map<String, Object> user : userList){
			
			fencodes = fenorgcode.split(",");
			usercodes = MapGetter.getString(user, "OrganizationCode").split(",");
			for(i=0;i<usercodes.length;i++){
				if(usercodes[i].length() <= vclorgcode.length()){
					//设备在用户所属多个机构和所有下级机构下
					if(usercodes[i].startsWith(vclorgcode.substring(0,usercodes[i].length()))){
						//围栏在用户所属多个机构和所有下级机构下
						int m = 0;
						for(h=0;h<fencodes.length;h++){
							if(usercodes[i].length() <= fencodes[h].length())
								if(usercodes[i].startsWith(fencodes[h].substring(0,usercodes[i].length()))){
									m = 1;
									break;
								}
						}
						if(1 == m){
							smsInfoDAO.IOSMSInfo(sqlList, paramList, user, smsSendTimeList,SMSContent,"15");
							updatePromptMsgUser(sqlList,paramList,user,gpstime,vclid,iofen);
							continue;
						}
					}
				}
			}
		}
	}
	
	private void updatePromptMsgUser(List<String> sqlList,
			List<Object[]> paramList, Map<String, Object> user, String gpstime, String vclid,String iofen) {
		sqlList.add("update Msg_Prompt set MsgP_SMSRecUser = ISNULL(MsgP_SMSRecUser,'，')+?+'，'"
				+ " where MsgP_Type =15 and Msgp_SecTypeID=? and MsgP_VclId=? and MsgP_MsgTime = ? and MsgP_MsgCID=IDENT_CURRENT('PassFenTip')");
		paramList.add(new Object[] { MapGetter.getString(user, "username"),iofen,vclid,gpstime });
		
	}

	/**
	 * 插入数据到围栏最新表	20141016jinjiayu
	 * @param vclid		设备id
	 * @param fenid		围栏id
	 * @param gpstime	进出围栏时间
	 * @param sqlList	
	 * @param paramList
	 */
	public void insertFenLast(String vclid, String fenid, String gpstime, List<String> sqlList,List<Object[]> paramList) {

		String sb = "insert into VclFenState_Last values(?,?,?) ";
		
		sqlList.add(sb);
		paramList.add(new Object[] { vclid,fenid,gpstime });
		
		return ;
	}
	
	/**
	 * 执行SQL
	 * @param sqlList
	 * @param paramList
	 * @return
	 */
	public boolean tranSQLs(List<String> sqlList,List<Object[]> paramList){
		return this.tranDAO.batch(sqlList, paramList);
	}

	/**
	 * 获取最新位置在时效围栏外的设备集合
	 * @param termSoftVer414 JPP设备
	 * @return
	 */
	public List<Map<String, Object>> getVcls(String termSoftVer414) {
		
		/*String sql = "select ISNULL(fltMR_MsgF_ID,0) id from fltMsgRecord where fltMR_TableName='EFF'";
		Map<String,Object> mapID = baseDAO.execQueryOne(sql, new Object[]{});*/
		
		List<Map<String, Object>> list = null;
		
		StringBuffer sb = new StringBuffer("SELECT VclPL_ID,vclpl_vcl_id,VehicleNumber,fenceid,VclPL_MsgID,VclPL_EgnState,VclPL_Lo,VclPL_La,VclPL_Speed,VclPL_PstnTime,EffC_SpeedLowValue,EffC_SpeedLowTime,EffC_ShutDownTime");
		sb.append(" ,infen = CASE WHEN vclpl_lo >= OrgFen_RectLeftTX AND vclpl_lo <= OrgFen_RectRightBX AND vclpl_la <= OrgFen_RectLeftTY AND vclpl_la >= OrgFen_RectRightBY THEN 1 ELSE 0 END,VclPS_isOpen");
		sb.append(" FROM cty_vcl.dbo.vcl_pstn_last");
		sb.append(" LEFT JOIN v_vehicleinfo ON vehicleid = vclpl_vcl_id");
		sb.append(" LEFT JOIN effoline ON effol_vclid = vclpl_vcl_id");
		sb.append(" LEFT JOIN organization_fencelist ON fenceid = effol_fenceid");
		sb.append(" LEFT JOIN eff_config ON effc_vclid = vclpl_vcl_id");
		sb.append(" left join " +GlobalDBVar.getDBFullName_Conn("VclPromptSwitch","1",null)+
				" on vclpl_vcl_id=VclPS_VclID and VclPS_Type='7'");
		//sb.append(" WHERE VclPL_MsgTp_ID=2 AND effc_isopen = 1 AND effc_speedlowvalue > 0 AND effc_speedlowtime > 0 AND effc_shutdowntime > 0 and VclPL_ID>?");
		sb.append(" WHERE UsefulLife > GETDATE() AND effc_isopen = 1 AND effc_speedlowvalue > 0 AND effc_speedlowtime > 0 AND effc_shutdowntime > 0");
		sb.append(" AND Tmnl_SoftEdition IN ("+termSoftVer414.toString()+");");
		
		list = this.baseDAO.execQuery(sb.toString(),new Object[] { });
		//list = this.baseDAO.execQuery(sb.toString(),new Object[] {MapGetter.getString(mapID, "id") });
		
		/*sql = "select MAX(VclPL_ID) id from cty_vcl.dbo.vcl_pstn_last";
		mapID = baseDAO.execQueryOne(sql, new Object[]{});
		
		sql = "update fltMsgRecord set fltMR_MsgF_ID=? where fltMR_TableName='EFF'";
		baseDAO.execUpdate(sql, new Object[]{MapGetter.getString(mapID, "id") });*/
		
		return list;
	}

	/**
	 * 从提示中心获取设备最后一次出围栏提示信息集合
	 * @param termSoftVer414 JPP设备
	 * @return
	 */
	public Map<String,List<Map<String,Object>>> getPromts(String termSoftVer414) {
		
		List<Map<String, Object>> list = null;
		
		//StringBuffer sb = new StringBuffer("select mp.MsgP_VclID,mp.MsgP_Type,mp.Msgp_SecTypeID,mp.msgp_msgtime,MsgP_Position,MsgP_DetailMsg");
		/*StringBuffer sb = new StringBuffer("select mp.MsgP_VclID,mp.Msgp_SecTypeName,mp.MsgP_MsgTime");
		sb.append(" from Msg_Prompt as mp");
		sb.append(" join (select max(MsgP_MsgTime) as msgp_msgtime,MsgP_VclID,MsgP_Type,Msgp_SecTypeID from Msg_Prompt");
		sb.append(" where MsgP_Type=15 and MsgP_DetailMsg is not null and Msgp_SecTypeID=0");
		sb.append(" group by MsgP_VclID,MsgP_Type,Msgp_SecTypeID) temp1	");
		sb.append(" on temp1.msgp_msgtime = mp.MsgP_MsgTime and temp1.MsgP_VclID = mp.MsgP_VclID and temp1.MsgP_Type = mp.MsgP_Type and temp1.Msgp_SecTypeID = mp.Msgp_SecTypeID");
		sb.append(" left join V_VehicleInfo on VehicleID=mp.MsgP_VclID");
		sb.append(" where mp.MsgP_Type=15 and mp.Msgp_SecTypeID=0 AND Tmnl_SoftEdition IN ("+termSoftVer414.toString()+")");*/
		StringBuffer sb = new StringBuffer("select PassFT_MsgTime,PassFT_VclID,PassFT_FenceID,PassFT_IsInto,ROW_NUMBER() over(PARTITION by PassFT_VclID order by PassFT_MsgTime desc) rowid");
		sb.append(" from PassFenTip LEFT JOIN v_vehicleinfo ON vehicleid = PassFT_VclID");
		sb.append(" where UsefulLife > GETDATE() and Tmnl_SoftEdition IN ("+termSoftVer414.toString()+")");
		sb.append(" group by PassFT_VclID,PassFT_MsgTime,PassFT_FenceID,PassFT_IsInto");
		
		list = this.baseDAO.execQuery(sb.toString(),new Object[] {});
		
		String tiptime = "";
		Map<String,Object> map_List = new HashMap<String, Object>();
		List<Map<String, Object>> list2 = new ArrayList<Map<String,Object>>();
		for(int i=0;i<list.size();i++){
			map_List = list.get(i);
			if("1".equals(MapGetter.getString(map_List, "rowid"))){
				tiptime = MapGetter.getString(map_List, "PassFT_MsgTime");
				if("false".equals(MapGetter.getString(map_List, "PassFT_IsInto")))
					list2.add(map_List);
			}else{
				if(tiptime.equals(MapGetter.getString(map_List, "PassFT_MsgTime")) && "false".equals(MapGetter.getString(map_List, "PassFT_IsInto"))){
					list2.add(map_List);
				}
			}
		}
		
		Map<String,List<Map<String,Object>>> map = null;
		map = new ListUtil().groupBy("PassFT_VclID",list2);
		
		return map;
	}

	/**
	 * 从提示中心获取关机提醒未接触的集合
	 * @param termSoftVer414 JPP设备
	 * @return
	 */
	public Map<String, Object> getEngOff(String termSoftVer414) {

		Map<String, Object> map = new HashMap<String,Object>();
		List<Map<String, Object>> list = null;
		
		String sb = "select MsgP_VclID,MsgP_MsgTime from Msg_Prompt left join V_VehicleInfo on VehicleID=MsgP_VclID where MsgP_Type=16 and Msgp_SecTypeID=2 and MsgP_State != 3 AND Tmnl_SoftEdition IN ("+termSoftVer414.toString()+") group by MsgP_VclID,MsgP_MsgTime";
		
		list = this.baseDAO.execQuery(sb,new Object[] {});
		
		for(Map<String, Object> map1 : list){
			map.put(MapGetter.getString(map1, "MsgP_VclID"), MapGetter.getString(map1, "MsgP_MsgTime"));
		}
		
		return map;
	}

	/**
	 * 解除关机提醒状态为开机
	 * @param vclid		设备id
	 * @param fenid		围栏id
	 * @param openTime 	开机时间
	 * @param sqlList
	 * @param paramList
	 */
	public void updateProType(String vclid, String fenid, String openTime, String fenzhong,List<String> sqlList,List<Object[]> paramList) {
		
		String sb = "update Msg_Prompt set MsgP_State='3',MsgP_ReleaseType='1',MsgP_ReleaseTime=?,MsgP_ReleaseCTime=?  where MsgP_VclID=? and MsgP_Type='16' and MsgP_State='1' and Msgp_SecTypeID='2'";

		sqlList.add(sb);
		paramList.add(new Object[] { openTime,Integer.parseInt(fenzhong),vclid });
		
	}

	/**
	 * 车速低持续时间内是否存在开机信息
	 * @param vclid		设备id
	 * @param sdt		车速低持续时间
	 * @param starttime	设备最新位置时间
	 * @return
	 */
	public int countOpen(String vclid, int sdt, String starttime) {
		
		int count = 0;
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		SimpleDateFormat formatter2 = new SimpleDateFormat("yyyyMMdd"); 
		Date date = new Date();
		String date2 = "";
		try {
			date = formatter.parse(starttime);
			date2 = formatter2.format(date);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		//String sb = "select COUNT(1) from cty_vcl.dbo.vcl_pstn_last where VclPL_Vcl_ID=? and VclPL_MsgTp_ID=2 and VclPL_EgnState=1 and DATEDIFF(S,VclPL_PstnTime,?) <?";
		/*String sb = "select COUNT(1) from cty_vcl.dbo.vcl_pstn_"+date2;
		sb += "  where VclP_Vcl_ID=? and VclP_MsgTp_ID=2 and VclP_EgnState=1 and DATEDIFF(S,VclP_PstnTime,?) <?";*/
		String sb = "select COUNT(1) from CTY_Egn_B.dbo.Msg_Timing_"+date2+" where MsgT_Vcl_ID=? and MsgT_KeyState='1' and MsgT_EgnState='1' and DATEDIFF(S,MsgT_MsgTime,?) <?";
		
		count = this.baseDAO.getCount(sb, new Object[] {vclid,date,sdt*60});
		
		return count;
	}

	/**
	 * 关机提醒、低速提醒
	 * @param map			设备信息
	 * @param SMSContentPro	短信内容
	 * @param senType		关机标识
	 * @param fenid			围栏id
	 * @param sqlList
	 * @param paramList
	 */
	public void closeAndLowTip(Map<String, Object> map, String SMSContentPro, String senType, String fenid, String state,List<String> sqlList, List<Object[]> paramList) {
		
		StringBuffer sb2 = new StringBuffer("insert into Msg_Prompt(MsgP_VclID,MsgP_MsgTime,MsgP_Type,MsgP_CreateTime,Msgp_SecTypeID,Msgp_SecTypeName");
		sb2.append(" ,MsgP_DetailMsg,MsgP_Position,MsgP_Lo,MsgP_La,MsgP_State,MsgP_MsgCID)");
		sb2.append(" values(?,?,'16',GETDATE(),?,?,?,?,?,?,?,IDENT_CURRENT('Msg_Prompt'))");
		
		sqlList.add(sb2.toString());
		paramList.add(new Object[] { 
				MapGetter.getString(map, "vclpl_vcl_id"),
				MapGetter.getString(map, "VclPL_PstnTime"),
				senType,
				"1".equals(senType)?"低速预警":"关机提示",
				SMSContentPro,
				MapGetter.getString(map, "Position"),
				MapGetter.getString(map, "VclPL_Lo"),
				MapGetter.getString(map, "VclPL_La"),
				state
				});
		return ;
	}

	/**
	 * 时效提醒发送短信
	 * @param sqlList
	 * @param paramList
	 * @param userList
	 * @param state 
	 * @param smsSendTimeList
	 * @param SMSContent
	 */
	public void effSMS(List<String> sqlList, List<Object[]> paramList,
			List<Map<String, Object>> userList,String gpstime,String vclid,String sentype, String state, List<String> smsSendTimeList,
			String SMSContent) {
		
		for(Map<String, Object> user : userList){
			smsInfoDAO.IOSMSInfo2(sqlList, paramList, user, smsSendTimeList,SMSContent,"16",gpstime,vclid,sentype,state);
			updatePromptMsgUser2(sqlList,paramList,user,gpstime,vclid,sentype,state);
		}
		
	}
	
	private void updatePromptMsgUser2(List<String> sqlList,
			List<Object[]> paramList, Map<String, Object> user, String gpstime, String vclid,String sentype,String state) {
		String sql = "update Msg_Prompt set MsgP_SMSRecUser = ISNULL(MsgP_SMSRecUser,'，')+?+'，'"
				+ " where MsgP_Type =16 and Msgp_SecTypeID=? and Msgp_State=? and MsgP_VclId=? and MsgP_MsgTime = ?";
		if("3".equals(state)){
			sql +=" and MsgP_ID=(select max(msgp_id) msgp_id from msg_prompt where MsgP_VclID="+vclid+" and MsgP_Type=16 and MsgP_MsgTime='"+gpstime+"' and MsgP_SecTypeId=2 and MsgP_State=1)";
		}else{
			sql += " and MsgP_ID=IDENT_CURRENT('Msg_Prompt')";
		}
		sqlList.add(sql);
		paramList.add(new Object[] { MapGetter.getString(user, "username"),sentype,state,vclid,gpstime});
		
	}

	/**
	 * 获取在“车速低持续时间内”是否有关机信息或者是否有车速大于“车速低提醒值”
	 * @param vclid			设备id
	 * @param starttime		设备最新位置时间
	 * @param slt			车速低持续时间
	 * @param slv			车速低提醒值
	 * @return
	 */
	public int lowEffTip(String vclid, String starttime, int slt, int slv) {
		int count = 0;
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		SimpleDateFormat formatter2 = new SimpleDateFormat("yyyyMMdd"); 
		Date date = new Date();
		String date2 = "";
		try {
			date = formatter.parse(starttime);
			date2 = formatter2.format(date);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		/*String sb = "select COUNT(1) from cty_vcl.dbo.vcl_pstn_"+date2;
		sb += " where VclP_Vcl_ID=? and DATEDIFF(S,VclP_PstnTime,?) <? and ((VclP_MsgTp_ID=2 and VclP_EgnState=0) or VclP_Speed>?)";*/
		String sb  = "select COUNT(1) from CTY_Egn_B.dbo.Msg_Timing_"+date2+" where MsgT_Vcl_ID=? and ((MsgT_KeyState='1' and MsgT_EgnState='0') or MsgT_KeyState='0' or isnull(MsgT_KeyState,'')<>'' or isnull(MsgT_EgnState,'')<>'') and DATEDIFF(S,MsgT_MsgTime,?) <?";
		
		count = this.baseDAO.getCount(sb, new Object[] {vclid,date,slt*60});
		
		if(count>0){
			return count;
		}else{
			sb = "select COUNT(1) from cty_vcl.dbo.vcl_pstn_"+date2;
			sb += " where VclP_Vcl_ID=? and DATEDIFF(S,VclP_PstnTime,?) <? and VclP_Speed>?";
			count = this.baseDAO.getCount(sb, new Object[] {vclid,date,slt*60,slv});
		}
		
		return count;
	}

	/**
	 * 获取在关机提醒时间内是否有开机记录
	 * @param vclid
	 * @param starttime
	 * @param slt
	 * @param slv
	 * @return
	 */
	public String getEffTime(String vclid, String starttime, int slt, int slv) {
		
		Map<String,Object> map = null;
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		SimpleDateFormat formatter2 = new SimpleDateFormat("yyyyMMdd"); 
		Date date = new Date();
		String date2 = "";
		try {
			date = formatter.parse(starttime);
			date2 = formatter2.format(date);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		String sb = "select top 1 VclP_PstnTime from cty_vcl.dbo.vcl_pstn_"+date2;
		sb += " where VclP_Vcl_ID=? and ((VclP_MsgTp_ID=2 and VclP_EgnState=1) or VclP_Speed>?) order by VclP_PstnTime desc)";
		
		map = this.baseDAO.execQueryOne(sb, new Object[] {vclid,slv});
		
		return MapGetter.getString(map, "VclP_PstnTime");
	}
	
	/**
	 * 获取最近关机时间
	 * @param vclid
	 * @param starttime
	 * @param slt
	 * @param slv
	 * @return
	 */
	public String getEffTime2(String vclid, String starttime, int slt, int slv) {
		
		Map<String,Object> map = null;
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		SimpleDateFormat formatter2 = new SimpleDateFormat("yyyyMMdd"); 
		Date date = new Date();
		String date2 = "";
		try {
			date = formatter.parse(starttime);
			date2 = formatter2.format(date);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		String sb = "select top 1 VclP_PstnTime from cty_vcl.dbo.vcl_pstn_"+date2;
		sb += " where VclP_Vcl_ID=? and VclP_MsgTp_ID=2 and VclP_EgnState=0 order by VclP_PstnTime desc";
		
		map = this.baseDAO.execQueryOne(sb, new Object[] {vclid});
		
		return MapGetter.getString(map, "VclP_PstnTime");
	}

	public Map<String, Object> getPromtsP(String termSoftVer414) {
		List<Map<String, Object>> list = null;
		Map<String,Object>  map = new HashMap<String, Object>();
		
		StringBuffer sb = new StringBuffer("select MsgP_VclID,MsgP_MsgTime,ROW_NUMBER() over(PARTITION by MsgP_VclID order by MsgP_MsgTime desc) rowid");
		sb.append(" from Msg_Prompt where MsgP_Type='16' and MsgP_SecTypeId='1' group by MsgP_VclID,MsgP_MsgTime");
		
		list = this.baseDAO.execQuery(sb.toString(),new Object[] {});
		
		for(Map<String,Object> map_List :list){
			if("1".equals(MapGetter.getString(map_List, "rowid")))
				map.put(MapGetter.getString(map_List, "MsgP_VclID"), MapGetter.getString(map_List, "MsgP_MsgTime"));
		}
		
		return map;
	}
	
	/**
	 * /从定时参数表获取发动机状态
	 * @return
	 */
	public Map<String, Object> getMsgEgnState() {
		List<Map<String, Object>> list = null;
		Map<String,Object>  map = new HashMap<String, Object>();
		
		StringBuffer sb = new StringBuffer("select MsgTL_Vcl_ID,isnull(MsgTL_EgnState,0) MsgTL_EgnState,isnull(MsgTL_KeyState,0) MsgTL_KeyState from CTY_Egn_B.dbo.Msg_Timing_Last");
		
		list = this.baseDAO.execQuery(sb.toString(),new Object[] {});
		
		for(Map<String,Object> map_List :list){
			if("1".equals(MapGetter.getString(map_List, "MsgTL_EgnState")) && "1".equals(MapGetter.getString(map_List, "MsgTL_KeyState"))){
				map.put(MapGetter.getString(map_List, "MsgTL_Vcl_ID"), "1");
			}else{
				map.put(MapGetter.getString(map_List, "MsgTL_Vcl_ID"), "0");
			}
		}
		
		return map;
	}
}
